/*
-- =============================================
-- Author:			Jacqueline Walton
-- Create date:		March 27, 2014
-- Description:		Helpful Dashbaord Metric Scripts
-- Last Updated:	March 27, 2014

-- License and Permissions
					 *     https://crystaltest.codeplex.com
					 *
					 * Distributed in whole under the terms of the Apache 2.0 License
					 *
					 *     Copyright 2014, Pixeltrix
					 *
					 * Licensed under the GNU General Public License version 2 (GPLv2) (the "License");
					 * you may not use this file except in compliance with the License.
					 * You may obtain a copy of the License at
					 * 
					 *     https://crystaltest.codeplex.com/license
					 * 
					 * Unless required by applicable law or agreed to in writing, software
					 * distributed under the License is distributed on an "AS IS" BASIS,
					 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
					 * See the License for the specific language governing permissions and
					 * limitations under the License.
					 *
					 *     Date: Fri Mar 28 2014 09:33:33 -0500
-- =============================================
*/

/*
=============================================================================================
Find Test Cases Set to child in Automated field but no record in autoTestCaseMap
=============================================================================================
*/

select * from testcases 
left join (select distinct Projectabbreviation, childtestcaseid from dbo.AutoTestCaseMap) Childtestcases
	on TestCases.testcaseid = childtestcaseid and TestCases.Projectabbreviation = Childtestcases.Projectabbreviation
where TestCases.projectabbreviation = 'CT' and automated = 'Child' and childtestcaseid is null

/*
=============================================================================================
Find Test Cases Not set to child in Automated field but there is a record in autoTestCaseMap
=============================================================================================
*/

select * from testcases 
left join (select distinct Projectabbreviation, childtestcaseid from dbo.AutoTestCaseMap) Childtestcases
	on TestCases.testcaseid = childtestcaseid and TestCases.Projectabbreviation = Childtestcases.Projectabbreviation
where TestCases.projectabbreviation = 'CT' and automated not in ( 'Child', 'Yes') and childtestcaseid is not null

select automated, count(*) from testcases where projectabbreviation = 'CT' group by automated

select count(*) from testcases where projectabbreviation = 'CT' and autotestclass is not null and autotestclass != ''

/*
=============================================================================================
Find Test Cases Not set to Yes in Automated field but there is a value in autoTestClass field
=============================================================================================
*/

select * from testcases where projectabbreviation = 'CT' and automated != 'Yes' and autotestclass is not null and autotestclass != ''


/*
=============================================================================================
Find Test Cases Not set to Yes in Automated field but there is not a value in autoTestClass field
=============================================================================================
*/

select * from testcases where projectabbreviation = 'CT' and automated = 'Yes' and autotestclass is null or autotestclass = ''

select * from autoTestCaseMap where projectabbreviation = 'CT' and childTestCaseId = '988'

SELECT distinct testcaseid, browserabbreviation
FROM TestResults
where testdate >= '10/1/2013'
and testdate < '10/11/2013'
and environment = 'Stage'


	Order By testDate DESC
	
	
	select * from AutoTestCaseMap
	select distinct projectabbreviation, childtestcaseid from dbo.AutoTestCaseMap
select TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status, COUNT(RunID) as TestCount 
from TestResults join 

(select max(testRunId) as RunID, projectAbbreviation, environment, browserAbbreviation, testCaseId from TestResults where status in ('Pass', 'Fail')  and DATEADD(dd, DATEDIFF(dd,0,testDate), 0) >= '10/10/2013' group by projectAbbreviation, environment, browserAbbreviation, testcaseID )
 LastTestResults 
 
 on testRunID = LastTestResults.RunID 
 join TestCases on TestResults.projectAbbreviation = TestCases.projectAbbreviation and TestResults.testCaseId = TestCases.testCaseId 
 where TestResults.projectAbbreviation = 'CT' and TestResults.environment = 'QA' group by TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status order by TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status
 
 
 
 
 "select TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status, COUNT(RunID) as TestCount from TestResults join (select max(testRunId) as RunID, projectAbbreviation, environment, browserAbbreviation, testCaseId from TestResults where status in ('Pass', 'Fail')  and DATEADD(dd, DATEDIFF(dd,0,testDate), 0) >= '10/10/2013' group by projectAbbreviation, environment, browserAbbreviation, testcaseID ) LastTestResults on testRunID = LastTestResults.RunID join TestCases on TestResults.projectAbbreviation = TestCases.projectAbbreviation and TestResults.testCaseId = TestCases.testCaseId where TestResults.projectAbbreviation = 'CT' and TestResults.environment = 'QA' group by TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status order by TestResults.projectAbbreviation, TestResults.browserAbbreviation, TestResults.status"